package com.bening.smsapp.dao;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.log4j.*;

import com.bening.smsapp.bean.FixedLengthAsciiBean;
import com.bening.smsapp.bootstrap.BootConfigParameters;
import com.bening.smsapp.constant.PropertiesConstant;
import com.bening.smsapp.factory.SmsAppFactory;

public class SmsAppSQLLDRImpl implements SmsAppDao {

	private static BootConfigParameters bootParams = BootConfigParameters.getInstance();
	final Logger logger = Logger.getLogger( SmsAppSQLLDRImpl.class );
	
	public List getFileList(int flagType) {
		// TODO Auto-generated method stub
		return null;
	}
	public void updateFileListFlag(int updateTo, List fileList) {
		// TODO Auto-generated method stub
		
	}
	public void insertParsingAsciiRejected(String filename, String exception, String callReff ,int lineNumber){
		PreparedStatement prepStmt = null;
        Connection conn;
        conn = SmsAppFactory.getDbConnection();

        String sql = bootParams.getQueryProperties().getProperty(PropertiesConstant.SQL_INSERT_CDR_REJECTED);
        try {
        	prepStmt = conn.prepareStatement(sql);
        	prepStmt.setString(1, filename);
        	prepStmt.setString(2, exception);
        	prepStmt.setString(3, callReff);
        	prepStmt.setInt(4, lineNumber);
        	prepStmt.executeUpdate();
        }catch(SQLException s) {
        	logger.error("Fail to insert core_cdr_rejected ",s);
        }finally {
        	try {
	        	prepStmt.close();
	        	conn.close();
        	}catch(SQLException s) {
        		logger.error("Error closing connection-finally block");
        	}
        }
        
        
        
	}
	public void insertParsingResultAscii(List parseResult, String filename) throws SQLException{
		// TODO Auto-generated method stub
        String callReff = "";
        logger.info(Thread.currentThread().getName()+" Inserting "+parseResult.size()+" data");
        int throwenNull = 0;
        int throwenCallReffNull = 0;
        String csvFile = bootParams.getTempDir()+"/"+filename;
        try {
        	FileWriter fw = new FileWriter(new File(csvFile));
        	StringBuffer prepStmt = new StringBuffer();
	        for(int i=0;i<parseResult.size();i++) {
	        		FixedLengthAsciiBean f = (FixedLengthAsciiBean)parseResult.get(i);
	        		if(f!=null) {
		        		if(f.getCallRef()==null) {
		        			f.setCallRef("");
		        		}
				        	prepStmt.append(f.getCallRef());
				        	prepStmt.append(",");
				        	prepStmt.append(filename);
				        	prepStmt.append(",");
				        	prepStmt.append(f.getRecType());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getStructCode());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getClgNpi());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getClgNumber());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getClgCustId());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getCldNpi());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getCldNumber());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getCldCustId());
				        	prepStmt.append(",");
				        	prepStmt.append(toDateString(f.getMsgSubTime()));
				        	prepStmt.append(",");
				        	prepStmt.append(toDateString(f.getMsgDlrTime()));
				        	prepStmt.append(",");
				        	prepStmt.append(f.getMsgStatus());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getMsgLength());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getPriority());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getDeferred());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getReceipt());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getNbAttempt());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getProtId());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getClgMscFlag());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getClgMscGt());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getCldMscFlag());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getCldMscGt());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getMapError());
				        	prepStmt.append(",");
				        	prepStmt.append(0);
				        	prepStmt.append(",");
				        	prepStmt.append(0);
				        	prepStmt.append(",");
				        	prepStmt.append(f.getClgImsi());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getCldImsi());
				        	prepStmt.append(",");
				        	prepStmt.append("");
				        	prepStmt.append(",");
				        	prepStmt.append(f.getClgFlag());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getCldFlag());
				        	prepStmt.append(",");
				        	prepStmt.append(f.getLocalError());
				        	prepStmt.append(System.getProperty("line.separator"));
			        		
		        		}else {
		        			throwenNull++;
		        		}
		        	}
	        	fw.write(prepStmt.toString());
		        fw.flush();
	    		fw.close();
        	}catch(IOException s) {
        		logger.error(s);
        		
        	}catch(NullPointerException n) {
        		throwenNull++;
        		n.printStackTrace();
        	}
        	logger.info(Thread.currentThread().getName()+"-Throwing null :"+throwenNull);
        	logger.info(Thread.currentThread().getName()+"-Throwing callreff null :"+throwenCallReffNull);
        	generateControlFile(filename);
        	execLoader(csvFile);
	}
	
	public void insertParsingResultAsn1(List beanList, String filename) throws SQLException{
/*        PreparedStatement prepStmt = null;
        Connection conn;
        conn = SmsAppFactory.getDbConnection();

        String sql = bootParams.getQueryProperties().getProperty(PropertiesConstant.SQL_INSERT_LOGICA);
        prepStmt = conn.prepareStatement(sql);
        conn.setAutoCommit(false);
        
        logger.info("Inserting "+beanList.size()+" data");
        try {
	        for(int i=0;i<beanList.size();i++) {
	        	Asn1Bean f = (Asn1Bean)beanList.get(i);
		        	
		        	prepStmt.append(1, filename);
		        	prepStmt.append(2, f.getOrigAddress());
		        	prepStmt.append(3, f.getOrigAddressTon());
		        	prepStmt.append(4, f.getOrigAddressNpi());
		        	prepStmt.append(5, f.getOrigAddressPid());
		        	prepStmt.append(6, f.getOrigAddressMsisdn());
		        	prepStmt.append(7, f.getOrigAddressGSM());
		        	prepStmt.append(8, f.getRecipAddress());
		        	prepStmt.append(9, f.getRecipAddressTon());
		        	prepStmt.append(10, f.getRecipAddressNpi());
		        	prepStmt.append(11, f.getRecipAddressPid());
		        	prepStmt.append(12, f.getRecipAddressMsisdn());
		        	prepStmt.append(13, f.getRecipAddressGSM());
		        	prepStmt.append(14, f.getSubmitDate());
		        	prepStmt.append(15, f.getSubmitTime());
		        	prepStmt.append(16, f.getStatus());
		        	prepStmt.append(17, f.getTerminDate());
		        	prepStmt.append(18, f.getTerminTime());
		        	prepStmt.append(19, f.getLengthOfMessage());
		        	prepStmt.append(20, f.getPrioIndicator());
		        	prepStmt.append(21, f.getValidityPeriod());
		        	prepStmt.append(22, f.getValidPeriodHours());
		        	prepStmt.append(23, f.getValidPeriodMinutes());
		        	prepStmt.append(24, f.getDeferIndicator());
		        	prepStmt.append(25, f.getDeferPeriod());
		        	prepStmt.append(26, f.getNotifIndicator());
		        	prepStmt.append(27, f.getNotifAddress());
		        	prepStmt.append(28, f.getNotifAddressTon());
		        	prepStmt.append(29, f.getNotifAddressNpi());
	
		        	prepStmt.append(30, f.getNotifAddressPid());
		        	prepStmt.append(31, f.getNotifAddressMsisdn());
		        	prepStmt.append(32, f.getNotifAddressGSM());
		        	prepStmt.append(33, f.getVsmscid());
		        	prepStmt.append(34, f.getVsmsctype());
		        	prepStmt.append(35, f.getDgtiAddress());
		        	prepStmt.append(36, f.getDgtiAddressGSM());
		        	prepStmt.append(37, f.getDestPointCode());
		        	prepStmt.append(38, f.getOgtiAddress());
		        	prepStmt.append(39, f.getOgtiAddressGSM());
		        	prepStmt.append(40, f.getOrigPointCode());
		        	prepStmt.append(41, f.getOrglSubmitDate());
		        	prepStmt.append(42, f.getOrglSubmitTime());
		        	prepStmt.append(43, f.getTransparentPid());
		        
		        	prepStmt.append(44, f.getMesgReplyPath());
		        	prepStmt.append(45, f.getRecipIntlMobileSubId());
		        	prepStmt.append(46, f.getCallingLineId());
		        	prepStmt.append(47, f.getCallingLineIdTon());
		        	prepStmt.append(48, f.getCallingLineIdNpi());
		        	prepStmt.append(49, f.getCallingLineIdPid());
		        	prepStmt.append(50, f.getCallingLineIdMsisdn());
		        	prepStmt.append(51, f.getCallingLineIdGSM());
		        	prepStmt.append(52, f.getConsolidation());
		        	prepStmt.append(53, f.getPortNumber());
		        	prepStmt.append(54, f.getAser());
		        	prepStmt.append(55, f.getMser());
		        	prepStmt.append(56, f.getNser());
		        	prepStmt.append(57, f.getXser());
		        	prepStmt.append(58, f.getOrigIntlMobileSubId());
		        	prepStmt.append(59, f.getBillid());
		        	prepStmt.append(60, f.getLang());
		        	
		        	prepStmt.append(61, f.getCbat());
		        	prepStmt.append(62, f.getPpPser());
		        	prepStmt.append(63, f.getPpAser());
		        	prepStmt.append(64, f.getPpAserDuringJam());
		        	prepStmt.append(65, f.getPpAserFree());
		        	prepStmt.append(66, f.getPpAserRecip());
		        	prepStmt.append(67, f.getSmeReference());
		        	prepStmt.append(68, f.getSmsContentDcs());
		        	prepStmt.append(69, f.getSmsContents());
		        	prepStmt.append(70, f.getCmReferenceNr());
		        	prepStmt.append(71, f.getCurrentSegment());
		        	prepStmt.append(72, f.getSegmentsTotal());
		        	prepStmt.append(73, f.getTextFormatting());
		        	prepStmt.append(74, f.getBytesCompressedData());
		        	prepStmt.append(75, f.getPredefinedAnimations());
		        	
		        	prepStmt.append(76, f.getUserDefinedAnimations());
		        	prepStmt.append(77, f.getPredefinedSounds());
		        	prepStmt.append(78, f.getUserDefinedSounds());
		        	prepStmt.append(79, f.getBlackWhitePictures());
		        	prepStmt.append(80, f.getStandardWvg());
		        	prepStmt.append(81, f.getCharacterSizeWvg());
		        	prepStmt.append(82, f.getGreyscalePictures());
		        	prepStmt.append(83, f.getColourPictures());
		        	prepStmt.append(84, f.getBusinessCards());
		        	prepStmt.append(85, f.getCalendarEntries());
		        	prepStmt.append(86, f.getPolyphonicMelodi());
		        	prepStmt.append(87, f.getBit8PortNumberDest());
		        	prepStmt.append(88, f.getBit16PortNumberDest());
		        	prepStmt.append(89, f.getLmsgNrSeg());
		        	prepStmt.append(90, f.getMessageReference());
		        	
		        	prepStmt.append(91, f.getBoolSer());
		        	prepStmt.append(92, f.getOrigLASN());
		        	prepStmt.append(93, f.getRecipLASN());
		        	prepStmt.append(94, f.getOrigMsgID());
		        	prepStmt.append(95, f.getRecipMsgID());
		        	prepStmt.append(96, f.getReceiptDate());
		        	prepStmt.append(97, f.getReceiptTime());
		        	prepStmt.append(98, f.getIsr());
		        	prepStmt.append(99, f.getRecipAltAddress());
		        	prepStmt.append(100, f.getRecipAltAddressTon());
		        	prepStmt.append(101, f.getRecipAltAddressNpi());
		        	prepStmt.append(102, f.getRecipAltAddressPid());
		        	prepStmt.append(103, f.getRecipAltAddressMsisdn());
		        	prepStmt.append(104, f.getGeneratedSegments());
		        	
		        	prepStmt.append(105, f.getServiceType());
		        	prepStmt.append(106, f.getDeliveryAttempts());
		        	prepStmt.append(107, f.getUntranslOrigAddress());
		        	prepStmt.append(108, f.getUntranslOrigAddressGSM());
		        	prepStmt.append(109, f.getUntranslRecipAddress());
		        	prepStmt.append(110, f.getUntranslRecipAddressGSM());
		        	prepStmt.append(111, f.getMsgError());
		        	prepStmt.append(112, f.getTpDCS());
		        	prepStmt.append(113, f.getGenericUrgencyLevel());
		        	prepStmt.append(114, f.getIfaceUrgencyLevel());
		        	prepStmt.append(115, f.getTeleserviceId());
		        	prepStmt.append(116, f.getOrigAddrGroup());
		        	prepStmt.append(117, f.getRecipAddrGroup());
		        	prepStmt.append(118, f.getOrigNetworkType());
		        	prepStmt.append(119, f.getRecipNetworkType());
		        	prepStmt.append(120, f.getCldFlag());
		        	prepStmt.append(121, f.getClgFlag());
		        	prepStmt.append(122, f.getDgtiTon());
		        	prepStmt.append(123, f.getDgtiPid());
		        	prepStmt.append(124, f.getDgtiNpi());
		        	prepStmt.append(125, f.getDgtiMsisdn());
		        	
		        	prepStmt.executeUpdate();   
		        	prepStmt.clearParameters();
		        	}
        	conn.commit();
        }catch(SQLException s) {
        	logger.error(s);
        }finally {
        	logger.info("finalle-closing");
        	prepStmt.close();
        	conn.close();
        }
*/	}
	
    private static String toDateString(Date date) {
    	if(date==null) return "";
    	
    	SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy hh:MM:ss");
    	return sdf.format(date);
    	
    }
    
    private synchronized void  execLoader(String filename) {

    	String cmd = bootParams.getSqlldrCmd()	.replaceAll("\\$control", filename+".ctl")
    											.replaceAll("\\$log", filename+".log");
    	logger.info(Thread.currentThread().getName()+"-Running Command : "+cmd);
    	try{
    		
    		Process p = Runtime.getRuntime().exec(cmd); // will run sqlldr
    		
    	}catch(Exception e){
    		System.out.println("exception from exec statement \n"+e); 
    	}
    }
    
    private static void generateControlFile(String filename) {
    	String dataFile = new String(bootParams.getTempDir()+"/"+filename);
    	String ctlTemplate = bootParams.getAsciiCtlTemplate().replaceAll("\\$fileName", dataFile);    	
    	try {
    		FileWriter fw = new FileWriter(new File(dataFile+".ctl"));
        	fw.write(ctlTemplate);
	        fw.flush();
    		fw.close();
    	}catch(IOException i) {
    		i.printStackTrace();
    	}
    }
}
